
PRINT '*********************************************************************'
PRINT '*** INSTALLATION STARTED ' + CAST(GETDATE() AS VARCHAR(255)) + ' ***'
PRINT '*********************************************************************'


USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------

--Enable CLR Integration if is not enabled
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'show advanced options' AND [value] = 1) BEGIN 
	EXEC sp_configure 'show advanced options', 1;
	RECONFIGURE;
	PRINT '*** Enable SQL Advanced Options - Completed with SUCCESS ***'
END

IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'clr enabled' AND [value] = 1) BEGIN 
	EXEC sp_configure 'clr enabled', 1
	RECONFIGURE
	PRINT '*** Enable CLR - Completed with SUCCESS ***'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_object_filegroup' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].[fn_object_filegroup]
	PRINT '*** DROP FUNCTION [clr].[fn_object_filegroup] - Completed with SUCCESS ***'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_object_partitionscheme' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].[fn_object_partitionscheme]
	PRINT '*** DROP FUNCTION [clr].[fn_object_partitionscheme] - Completed with SUCCESS ***'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_object_partitioncolumn' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].[fn_object_partitioncolumn]
	PRINT '*** DROP FUNCTION [clr].[fn_object_partitioncolumn] - Completed with SUCCESS ***'
END


--Drop the assembly if it exist
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ClrObjDef') BEGIN 
	DROP ASSEMBLY [ClrObjDef]
	PRINT '*** DROP Assembly ClrObjDef - Completed with SUCCESS ***'
END

-- Create the assembly if it does not exists
CREATE ASSEMBLY [ClrObjDef] 
FROM 'C:\Program Files\Microsoft SQL Server\100\CLR\ClrObjDef.dll' 
-- testing only
--FROM 'D:\Projects\SqlLib\Project\CLR\ObjDef\bin\Debug\ClrObjDef.dll' 
WITH PERMISSION_SET = SAFE
	
PRINT '*** CREATE Assembly ClrRegEx - Completed with SUCCESS ***'


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			[clr].[fn_object_filegroup]
Type:			Custom CLR UDF
Owner:					
Description:	DDL function, returns the name of the object's filegroup  
Parameters:		@object_name
				@object_parent
				@object_database 
				@object_type   
Dependens on:  	ClrObjDef.dll
Usage:			SELECT clr.fn_object_filegroup('Trans','dbo','Stage','U')	
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_object_filegroup&referringTitle=Documentation		
Version:		20121030
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/30/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].[fn_object_filegroup] 
	( @object_name SYSNAME
	 ,@object_type NCHAR(1) = 'U'
	 ,@object_parent SYSNAME = 'dbo'
	 )
RETURNS NVARCHAR(4000)	
AS EXTERNAL NAME ClrObjDef.ClrDDL.fn_object_filegroup

GO


---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_object_filegroup' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '*** CREATE FUNCTION [clr].[fn_object_filegroup] - Completed with SUCCESS ***'
END

ELSE
	PRINT '*** CREATE FUNCTION [clr].[fn_object_filegroup] - Completed with ERROR ***'

----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

GO


/*================================================================================================
Name:			[clr].[fn_object_partitionscheme]
Type:			Custom CLR UDF
Owner:					
Description:	DDL function, returns the name of the object's partition scheme  
Parameters:		@object_name
				@object_parent
				@object_database 
				@object_type   
Dependens on:  	ClrObjDef.dll
Usage:			SELECT clr.fn_object_partitionscheme('Trans','dbo','Stage','U')	
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_object_partitionscheme&referringTitle=Documentation		
Version:		20121102
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		11/02/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].[fn_object_partitionscheme] 
	( @object_name SYSNAME
	 ,@object_type NCHAR(1) = 'U'
	 ,@object_parent SYSNAME = 'dbo'
	 )
RETURNS NVARCHAR(4000)	
AS EXTERNAL NAME ClrObjDef.ClrDDL.fn_object_partitionscheme

GO


---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_object_partitionscheme' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '*** CREATE FUNCTION [clr].[fn_object_partitionscheme] - Completed with SUCCESS ***'
END

ELSE
	PRINT '*** CREATE FUNCTION [clr].[fn_object_partitionscheme] - Completed with ERROR ***'

----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

GO 

/*================================================================================================
Name:			[clr].[fn_object_partitioncolumn]
Type:			Custom CLR UDF
Owner:					
Description:	DDL function, returns the name of the object's partition scheme  
Parameters:		@object_name
				@object_parent
				@object_database 
				@object_type   
Dependens on:  	ClrObjDef.dll
Usage:			SELECT clr.fn_object_partitioncolumn('Trans','dbo','Stage','U')	
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_object_partitioncolumn&referringTitle=Documentation		
Version:		20121102
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		11/02/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].[fn_object_partitioncolumn] 
	( @object_name SYSNAME
	 ,@object_parent SYSNAME = 'dbo'
	 )
RETURNS NVARCHAR(4000)	
AS EXTERNAL NAME ClrObjDef.ClrDDL.fn_object_partitioncolumn

GO


---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_object_partitioncolumn' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '*** CREATE FUNCTION [clr].[fn_object_partitioncolumn] - Completed with SUCCESS ***'
END

ELSE
	PRINT '*** CREATE FUNCTION [clr].[fn_object_partitioncolumn] - Completed with ERROR ***'

----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

GO 

PRINT '*********************************************************************'
PRINT '*** INSTALLATION COMPLETE ' + CAST(GETDATE() AS VARCHAR(255)) + ' ***'
